import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CustomerDBManager extends CustomerManager {

	public CustomerDBManager(Connection initConnection, PropertyDBManager propertyDBManager, RankDBManager rankDBManager) {
		// empty constructor
		super(initConnection, propertyDBManager, rankDBManager);
	}

	public Long addCustomer(Customer newCustomer, double moneyAmount) throws DBManagerException {
		// add new Customer to customers table
		// function receive Customer object , moneyAmount for get customer rank
		// propertyDBManager for get customer id
		// rankDBManager for get customer rank
		// return TRUE ( success ) - customer was added to the customers table
		// FALSE ( problem add new customer )

		PreparedStatement pstmt = null; // define prepared statement
		int rowsInserted = 0; // number of inserted rows
		Long custId = null;
		Connection conn = getConnection(); // receive connection from Customer
		// Manager

		try {
			pstmt = conn.prepareStatement("INSERT INTO customers (cust_id,name,rank,password,phone,email,address,comments) VALUES (?,?,?,?,?,?,?,?)");

			// get customer id for new customer from propertDBManager
			custId = propertyDBManager.getNextValue("customer_sequence");
			pstmt.setString(1, custId.toString());

			pstmt.setString(2, newCustomer.getName());

			// get customer rank for new customer from rankDBManager
			Long rank = rankDBManager.getRankByMoneyAmount(moneyAmount);
			pstmt.setString(3, rank.toString());

			Long password = newCustomer.getPassword();
			pstmt.setString(4, password.toString());

			pstmt.setString(5, newCustomer.getPhone());
			pstmt.setString(6, newCustomer.getEmail());
			pstmt.setString(7, newCustomer.getAddress());
			pstmt.setString(8, newCustomer.getComments());

			rowsInserted = pstmt.executeUpdate(); // execute query

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (rowsInserted != 0) {
			System.out.println("[CustomerDBManager]: Success, new customer was created.");
		} else {
			throw new DBManagerException("[CustomerDBManager]: Error - please check customer inform.");
		}

		return custId;

	}

	public boolean deleteCustomer(Customer oldCustomer) throws DBManagerException {
		// delete customer from customers table
		// receive customer object
		// return TRUE ( success ) / FALSE ( problem delete old customer )

		PreparedStatement pstmt = null;
		Boolean flag = false;
		int rowsDeleted = 0; // number of deleted rows
		Connection conn = getConnection(); // receive connection from Customer
		// Manager

		try {
			pstmt = conn.prepareStatement("DELETE FROM customers WHERE cust_id =(?)");

			Long custId = oldCustomer.getCustId();
			pstmt.setString(1, custId.toString());

			rowsDeleted = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (rowsDeleted != 0) {
			System.out.println("[CustomerDBManager]: Success, customer was deleted ");
			flag = true;
		} else {
			throw new DBManagerException("[CustomerDBManager]: Incorrect data - can't delete customer!");
		}

		return flag;
	}

	public Customer getCustomerById(long custId) throws DBManagerException {
		// get Customer object by customer id

		ResultSet rset = null;
		Statement statement = null;
		Customer customer = new Customer(); // create new Customer Object
		Connection conn = getConnection(); // receive connection from Customer
		// Manager

		try {

			statement = conn.createStatement();
			String query = ("SELECT * FROM customers where cust_id =" + custId);
			rset = statement.executeQuery(query);

			if (rset.next() == true) {

				customer.setCustId(rset.getLong(1)); // set data for Customer
				// Object
				customer.setName(rset.getString(2));
				customer.setRank(rset.getLong(3));
				customer.setPassword(rset.getLong(4));
				customer.setPhone(rset.getString(5));
				customer.setEmail(rset.getString(6));
				customer.setAddress(rset.getString(7));
				customer.setComments(rset.getString(8));
			} else {
				throw new DBManagerException("[CustomerDBManager]: No found object in DB with cust_id =" + custId);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rset.close();
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		// TODO Auto-generated method stub

		return customer;
	}

	public boolean updateCustomerbyClientAction(Customer currCustomer) throws DBManagerException {
		// update personal inform : phone,email,address
		// Customer use this function for update personal inform

		PreparedStatement pstmt = null;
		int rowsUpdated = 0; // number of updated rows
		boolean flag = false;
		Connection conn = getConnection(); // receive connection from Customer
		// Manager

		try {
			pstmt = conn.prepareStatement("UPDATE customers SET phone=(?),email=(?),address=(?) WHERE cust_id = (?)");

			pstmt.setString(1, currCustomer.getPhone());
			pstmt.setString(2, currCustomer.getEmail());
			pstmt.setString(3, currCustomer.getAddress());

			Long custId = currCustomer.getCustId();
			pstmt.setString(4, custId.toString());

			rowsUpdated = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (rowsUpdated != 0) {
			System.out.println("[CustomerDBManager]: Success, personal inform was updated ");
			flag = true;
		} else {
			throw new DBManagerException("[CustomerDBManager]: Problem update data, Please check you inform.");
		}

		return flag;
	}

	public boolean updateCustomerbyAdminAction(Customer currCustomer) throws DBManagerException {

		// update rank and comments for customer
		// Administrator use this function for update Rank and Comments for
		// customer

		PreparedStatement pstmt = null;
		int rowsUpdated = 0; // number of updated rows
		boolean flag = false;
		Connection conn = getConnection(); // receive connection from Customer
		// Manager

		try {
			pstmt = conn.prepareStatement("UPDATE customers SET rank=(?),comments=(?) WHERE cust_id = (?)");

			Long rank = currCustomer.getRank();
			pstmt.setString(1, rank.toString());

			pstmt.setString(2, currCustomer.getComments());

			Long custId = currCustomer.getCustId();
			pstmt.setString(3, custId.toString());

			rowsUpdated = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (rowsUpdated != 0) {
			System.out.println("[CustomerDBManager]: Success, rank and comments were updated ");
			flag = true;
		} else {
			throw new DBManagerException("[CustomerDBManager]: Problem update data, Please check you inform.");
		}

		return flag;
	}

	public boolean checkPassword(long currPassword) throws DBManagerException {
		// check if password allready exist in customer table
		// return TRUE in case it's allready exist
		// return FALSE in case not exist

		boolean flag = false;
		ResultSet rset = null;
		Statement statement = null;
		Connection conn = getConnection(); // receive connection from Customer
		// Manager

		try {

			statement = conn.createStatement();
			String query = ("SELECT * FROM customers where password =" + currPassword);
			rset = statement.executeQuery(query);

			if (rset.next()) {
				flag = true;
				System.err.println("[CustomerDBManager]: Password " + currPassword + " -  allready in this DB. Please select another password.");
			}

		} catch (SQLException e) {
			throw new DBManagerException("[CustomerDBManager]: Error in checkPassword!");
		} finally {
			try {
				rset.close();
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		// TODO Auto-generated method stub

		return flag;

	}

}
